package com.scau.RBAC.dao.impl;

import com.scau.RBAC.dao.RoleDao;
import com.scau.RBAC.model.entity.Role;
import com.scau.RBAC.util.DBConnectionSingleton;
import com.scau.RBAC.util.DatabaseException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

public class MySqlRoleDao implements RoleDao {

    @Override
    public int insert(Role entity) {
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = DBConnectionSingleton.getInstance().getConnection();
            ps = conn.prepareStatement(
                    "INSERT INTO roles(role_name, role_code, description, status, created_time, updated_time, created_by, updated_by) VALUES(?, ?, ?, ?, ?, ?, ?, ?)");

            ps.setString(1, entity.getRoleName());
            ps.setString(2, entity.getRoleCode());
            ps.setString(3, entity.getDescription());
            ps.setInt(4, entity.getStatus() != null ? entity.getStatus() : 0);
            ps.setObject(5, entity.getCreatedTime());
            ps.setObject(6, entity.getUpdatedTime());
            ps.setLong(7, entity.getCreatedBy() != null ? entity.getCreatedBy() : 0);
            ps.setLong(8, entity.getUpdatedBy() != null ? entity.getUpdatedBy() : 0);

            return ps.executeUpdate();
        } catch (SQLException e) {
            throw new DatabaseException("插入角色失败", e);
        } finally {
            closeResources(ps, null);
        }
    }

    @Override
    public int batchInsert(List<Role> entities) {
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = DBConnectionSingleton.getInstance().getConnection();
            conn.setAutoCommit(false);

            ps = conn.prepareStatement(
                    "INSERT INTO roles(role_name, role_code, description, status, created_time, updated_time, created_by, updated_by) VALUES(?, ?, ?, ?, ?, ?, ?, ?)");

            for (Role role : entities) {
                ps.setString(1, role.getRoleName());
                ps.setString(2, role.getRoleCode());
                ps.setString(3, role.getDescription());
                ps.setInt(4, role.getStatus() != null ? role.getStatus() : 0);
                ps.setObject(5, role.getCreatedTime());
                ps.setObject(6, role.getUpdatedTime());
                ps.setLong(7, role.getCreatedBy() != null ? role.getCreatedBy() : 0);
                ps.setLong(8, role.getUpdatedBy() != null ? role.getUpdatedBy() : 0);
                ps.addBatch();
            }

            int[] results = ps.executeBatch();
            conn.commit();

            int total = 0;
            for (int result : results) {
                total += result;
            }
            return total;
        } catch (SQLException e) {
            try {
                if (conn != null) {
                    conn.rollback();
                }
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            throw new DatabaseException("批量插入角色失败", e);
        } finally {
            try {
                if (conn != null) {
                    conn.setAutoCommit(true);
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            closeResources(ps, null);
        }
    }

    @Override
    public int deleteById(Long id) {
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = DBConnectionSingleton.getInstance().getConnection();
            ps = conn.prepareStatement("DELETE FROM roles WHERE id = ?");
            ps.setLong(1, id);
            return ps.executeUpdate();
        } catch (SQLException e) {
            throw new DatabaseException("删除角色失败", e);
        } finally {
            closeResources(ps, null);
        }
    }

    @Override
    public int batchDelete(List<Long> ids) {
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = DBConnectionSingleton.getInstance().getConnection();
            conn.setAutoCommit(false);

            ps = conn.prepareStatement("DELETE FROM roles WHERE id = ?");

            for (Long id : ids) {
                ps.setLong(1, id);
                ps.addBatch();
            }

            int[] results = ps.executeBatch();
            conn.commit();

            int total = 0;
            for (int result : results) {
                total += result;
            }
            return total;
        } catch (SQLException e) {
            try {
                if (conn != null) {
                    conn.rollback();
                }
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            throw new DatabaseException("批量删除角色失败", e);
        } finally {
            try {
                if (conn != null) {
                    conn.setAutoCommit(true);
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            closeResources(ps, null);
        }
    }

    @Override
    public int update(Role entity) {
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = DBConnectionSingleton.getInstance().getConnection();
            ps = conn.prepareStatement(
                    "UPDATE roles SET role_name = ?, role_code = ?, description = ?, status = ?, updated_time = ?, updated_by = ? WHERE id = ?");

            ps.setString(1, entity.getRoleName());
            ps.setString(2, entity.getRoleCode());
            ps.setString(3, entity.getDescription());
            ps.setInt(4, entity.getStatus() != null ? entity.getStatus() : 0);
            ps.setObject(5, entity.getUpdatedTime());
            ps.setLong(6, entity.getUpdatedBy() != null ? entity.getUpdatedBy() : 0);
            ps.setLong(7, entity.getId());

            return ps.executeUpdate();
        } catch (SQLException e) {
            throw new DatabaseException("更新角色失败", e);
        } finally {
            closeResources(ps, null);
        }
    }

    @Override
    public Role findById(Long id) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = DBConnectionSingleton.getInstance().getConnection();
            ps = conn.prepareStatement("SELECT * FROM roles WHERE id = ?");
            ps.setLong(1, id);
            rs = ps.executeQuery();

            if (rs.next()) {
                return mapResultSetToRole(rs);
            }
            return null;
        } catch (SQLException e) {
            throw new DatabaseException("根据ID查询角色失败", e);
        } finally {
            closeResources(ps, rs);
        }
    }

    @Override
    public List<Role> findAll() {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = DBConnectionSingleton.getInstance().getConnection();
            ps = conn.prepareStatement("SELECT * FROM roles");
            rs = ps.executeQuery();

            List<Role> roles = new ArrayList<>();
            while (rs.next()) {
                roles.add(mapResultSetToRole(rs));
            }
            return roles;
        } catch (SQLException e) {
            throw new DatabaseException("查询所有角色失败", e);
        } finally {
            closeResources(ps, rs);
        }
    }

    @Override
    public List<Role> findByPage(Map<String, Object> params, int pageNum, int pageSize) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = DBConnectionSingleton.getInstance().getConnection();
            StringBuilder sql = new StringBuilder("SELECT * FROM roles WHERE 1=1");
            List<Object> paramList = new ArrayList<>();

            // 构建查询条件
            buildQueryConditions(sql, params, paramList);

            // 添加分页
            sql.append(" LIMIT ? OFFSET ?");
            paramList.add(pageSize);
            paramList.add((pageNum - 1) * pageSize);

            ps = conn.prepareStatement(sql.toString());

            // 设置参数
            for (int i = 0; i < paramList.size(); i++) {
                ps.setObject(i + 1, paramList.get(i));
            }

            rs = ps.executeQuery();
            List<Role> roles = new ArrayList<>();
            while (rs.next()) {
                roles.add(mapResultSetToRole(rs));
            }
            return roles;
        } catch (SQLException e) {
            throw new DatabaseException("分页查询角色失败", e);
        } finally {
            closeResources(ps, rs);
        }
    }

    @Override
    public long count(Map<String, Object> params) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = DBConnectionSingleton.getInstance().getConnection();
            StringBuilder sql = new StringBuilder("SELECT COUNT(*) FROM roles WHERE 1=1");
            List<Object> paramList = new ArrayList<>();

            // 构建查询条件
            buildQueryConditions(sql, params, paramList);

            ps = conn.prepareStatement(sql.toString());

            // 设置参数
            for (int i = 0; i < paramList.size(); i++) {
                ps.setObject(i + 1, paramList.get(i));
            }

            rs = ps.executeQuery();
            if (rs.next()) {
                return rs.getLong(1);
            }
            return 0;
        } catch (SQLException e) {
            throw new DatabaseException("统计角色数量失败", e);
        } finally {
            closeResources(ps, rs);
        }
    }

    @Override
    public List<Role> findByUserId(Long userId) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = DBConnectionSingleton.getInstance().getConnection();
            ps = conn.prepareStatement(
                    "SELECT r.* FROM roles r INNER JOIN user_roles ur ON r.id = ur.role_id WHERE ur.user_id = ?");
            ps.setLong(1, userId);
            rs = ps.executeQuery();

            List<Role> roles = new ArrayList<>();
            while (rs.next()) {
                roles.add(mapResultSetToRole(rs));
            }
            return roles;
        } catch (SQLException e) {
            throw new DatabaseException("根据用户ID查询角色失败", e);
        } finally {
            closeResources(ps, rs);
        }
    }

    @Override
    public Role findByRoleCode(String roleCode) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = DBConnectionSingleton.getInstance().getConnection();
            ps = conn.prepareStatement("SELECT * FROM roles WHERE role_code = ?");
            ps.setString(1, roleCode);
            rs = ps.executeQuery();

            if (rs.next()) {
                return mapResultSetToRole(rs);
            }
            return null;
        } catch (SQLException e) {
            throw new DatabaseException("根据角色代码查询角色失败", e);
        } finally {
            closeResources(ps, rs);
        }
    }

    // 辅助方法：将ResultSet映射到Role对象
    private Role mapResultSetToRole(ResultSet rs) throws SQLException {
        Role role = new Role();
        role.setId(rs.getLong("id"));
        role.setRoleName(rs.getString("role_name"));
        role.setRoleCode(rs.getString("role_code"));
        role.setDescription(rs.getString("description"));
        role.setStatus(rs.getInt("status"));
        role.setCreatedTime(rs.getObject("created_time", LocalDateTime.class));
        role.setUpdatedTime(rs.getObject("updated_time", LocalDateTime.class));
        role.setCreatedBy(rs.getLong("created_by"));
        role.setUpdatedBy(rs.getLong("updated_by"));
        return role;
    }

    // 辅助方法：构建查询条件
    private void buildQueryConditions(StringBuilder sql, Map<String, Object> params, List<Object> paramList) {
        if (params == null) {
            return;
        }

        if (params.containsKey("roleName")) {
            sql.append(" AND role_name LIKE ?");
            paramList.add("%" + params.get("roleName") + "%");
        }

        if (params.containsKey("roleCode")) {
            sql.append(" AND role_code LIKE ?");
            paramList.add("%" + params.get("roleCode") + "%");
        }

        if (params.containsKey("status")) {
            sql.append(" AND status = ?");
            paramList.add(params.get("status"));
        }
    }

    // 辅助方法：关闭资源
    private void closeResources(PreparedStatement ps, ResultSet rs) {
        try {
            if (rs != null) {
                rs.close();
            }
            if (ps != null) {
                ps.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}